package idv.arthur.work;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class CreateTP
 */
//@WebServlet(description = "新建測試案", urlPatterns = { "/CreateTP" })
public class CreateTP extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public CreateTP() {
        super();
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		Widgets wid = new Widgets();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String strSQL = "SELECT createdDate FROM tms_tp WHERE tpid=?";
		int tpid = Integer.parseInt(request.getParameter("tpid"));
		String tpName = request.getParameter("tpName");
		String tpOwner = request.getParameter("tpOwnerList");
		String tpComment = request.getParameter("tpComment");
		String tpcud = request.getParameter("tpcud");
		String returnResult = request.getParameter("tpcud");
		Timestamp timestamp = new Timestamp(new Date().getTime());
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy:MM:dd HH:mm:ss");
		
		try {
			
			/** 先確定這個計畫案是否存在 **/
			conn = wid.getConn(true);
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			rs = ps.executeQuery();
			
			if ( rs.next() ){
				strSQL = "UPDATE tms_tp SET tpName=?,comment=?,lastUpdatedDate=? WHERE tpid=?";
				ps = conn.prepareStatement(strSQL);
				ps.setString(1, tpName);
				ps.setString(2, tpComment);
				ps.setTimestamp(3, timestamp);
				ps.setInt(4, tpid);
				ps.execute();
				returnResult = sdf.format(timestamp);
			} else {
				/** 建立計畫案 **/
				strSQL = "INSERT INTO tms_tp (tpid,tpName,comment,pid,createdDate,lastUpdatedDate) VALUES (?,?,?,?,?,?)";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, tpName);
				ps.setString(3, tpComment);
				ps.setInt(4, 1);
				ps.setString(5, tpcud);
				ps.setString(6, tpcud);
				ps.execute();
			}
			
			/** 如果有舊的要先刪除 **/
			strSQL = "SELECT tpid FROM tms_tpowner WHERE tpid=?";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			rs = ps.executeQuery();
			if ( rs.next() ){
				strSQL = "DELETE FROM tms_tpowner WHERE tpid=?";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.execute();
				returnResult = sdf.format(timestamp);
			}
			
			/** 寫入對應的計畫案負責人 **/
			if ( !"".equalsIgnoreCase(tpOwner) ) {		//有輸入計畫案負責人才要處理
				String[] tpOwnerArray = null;
				if (tpOwner.contains(",")) {
					tpOwnerArray = tpOwner.split(",");	
				} else {
					tpOwnerArray = new String[1];
					tpOwnerArray[0] = tpOwner;
				}
				
				/** 不管有沒有舊的，新增或修改最後的動作都是新增 **/
				strSQL = "INSERT INTO tms_tpowner (tpid,userID) VALUES ("+tpid+",?)";	
				ps = conn.prepareStatement(strSQL);
				for (int i=0;i<tpOwnerArray.length;i++) {
					ps.clearParameters();
					ps.setString(1, tpOwnerArray[i]);
					ps.execute();
				}
			}
			
		} catch (SQLException e) {
			returnResult = wid.keepErrorMes(conn, ps, e, "", true);
		} finally {
			try {
				if (rs!= null) {rs.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				returnResult = e.toString();
			}
		}
		
		response.setContentType("application/text");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(returnResult);
	}

}
